Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Registering Applications

When you register an application, the name and the actions performed by that application are stored in the database to assist with debugging and performance tuning efforts. When an application is registered, its name and actions are recorded in the V$SESSION and V$SQLAREA views. This information can be used later to track problems.

To register an application, use the following procedures, available in the RDBMS_APPLICATION_INFO package:


Procedure Description

SET_MODULE Used to set the name of the module currently being run.
SET_ACTION Used to set the name of a certain action currently being performed.
SET_CLIENT_INFO Used to set up information for the client information field.
READ_MODULE Reads the current values of the module and action fields for the current session.
READ_CLIENT_INFO Reads the current client information field for the currently running session.

By registering the application, you can track many different parameters. Some of the values available through V$SQLAREA are given here:

  Memory used
  Number of sorts
  Number of executions
  Number of loads
  Number of parse calls
  Number of disk reads
  Number of buffer gets
  Number of rows processed

These parameters can provide valuable information when you are trying to debug various modules within your application. The information is enhanced by the addition of actions, which can further identify sections of your application.

Summary

Determining whether your SQL statements are properly optimized can be as important as anything else you can do to tune your system. An improperly tuned SQL statement can nullify any work you have done to optimize the database system. A well-tuned server system that is handling hundreds or thousands of unnecessary SQL statements can be perceived to have poor performance when, in reality, there is just an abundance of excess work being done.

The Oracle SQL Trace facility and the EXPLAIN PLAN command can be valuable tools in debugging inefficient SQL code. The SQL Trace facility and its companion program TKPROF can give valuable information into such areas as these:

  Parse, execute, and fetch counts
  CPU and elapsed times
  Physical and logical reads
  Number of rows processed
  Library cache misses

The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By analyzing the execution plan the Oracle optimizer has chosen, and by knowing your data and application, you should be able to determine whether the optimizer has chosen the correct execution plan for your application. If you do not agree with the execution plan that has been chosen, you can change it by modifying your SQL statements or by using hints (as described in Chapter 30, “Using Hints”).

EXPLAIN PLAN can help you rewrite your SQL statements to take better advantage of such things as indexes and hash keys. By analyzing the output, you may be able to provide hints that the Oracle optimizer can use to take better advantage of your knowledge of your data. By using hints, you may be able to take better advantage of features such as the Oracle Parallel Query option.

Together, SQL Trace, EXPLAIN PLAN, and application registration can all assist in optimizing your SQL statements and your application. By using these features, you can enhance the performance of your system.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.